﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Drawing;
using OfficeOpenXml;

namespace ExcelFileQueryTool
{
    public static class ExcelWorkSheetUtillty
    {
        private static int HisColor1 = Color.Red.ToArgb();
        private static int HisColor2 = Color.FromArgb(255, 192, 0, 0).ToArgb();
        private static string[] NonDataSheetNames = { "参数", "运输方式" };

        /// <summary>
        /// 是否为数据表（排除参数、运输方式、月结表之类的表）
        /// </summary>
        /// <returns></returns>
        public static bool IsDataSheet(this ExcelWorksheet sheet)
        {
            return NonDataSheetNames.Contains(sheet.Name) == false;
        }

        /// <summary>
        /// 表格是否已经结算完毕，仅仅作为历史记录保存
        /// </summary>
        /// <param name="sheet"></param>
        /// <returns></returns>
        public static bool IsHistorySheet(this ExcelWorksheet sheet)
        {
            var argb = sheet.TabColor.ToArgb();

            return sheet.Protection.IsProtected
                || argb == HisColor1
                || argb == HisColor2;
        }

        /// <summary>
        /// 是否汇总表
        /// </summary>
        /// <param name="sheet"></param>
        /// <returns></returns>
        public static bool IsConclusionSheet(this ExcelWorksheet sheet)
        {
            return sheet.Name.Contains("汇总");
        }

        public static bool DoesSheetRefNeedQuote(this ExcelWorksheet sheet)
        {
            // 工作表名以数字开头或者包含空格、以及以下字符的情况，跨表引用都将自动加'号，
            // $ % ` ~ ! @ # ^ & ( ) + - = , | " ; { }

            // 判断是否为数字开头
            var firstChar = sheet.Name[0];
            if (firstChar >= 48 && firstChar <= 57)
                return true;

            // 判断是否包含特殊字符
            foreach (var c in sheet.Name)
            {
                //if ((c >= 32 && c <= 47) || (c >= 58 && c <= 64))
                //    return true;

                switch (c)
                {
                    case ' ':
                    case '$':
                    case '%':
                    case '`':
                    case '~':
                    case '!':
                    case '@':
                    case '#':
                    case '^':
                    case '&':
                    case '(':
                    case ')':
                    case '+':
                    case '-':
                    case '=':
                    case ',':
                    case '|':
                    case '"':
                    case ';':
                    case '{':
                    case '}':
                        return true;
                    default:
                        break;
                }
            }

            return false;
        }

        public static ExcelWorksheet GetMainSheet(this ExcelWorkbook book)
        {
            if (book == null)
                throw new ArgumentNullException("book");

            var validSheets = book.Worksheets.Where(x => x.IsDataSheet() && x.IsHistorySheet() == false);
            var mainSheet = validSheets.Where(x => x.IsConclusionSheet()).FirstOrDefault();

            if (mainSheet == null)
                mainSheet = validSheets.First();

            return mainSheet;
        }
    }
}
